using System;
using System.Collections.Generic;
using System.Text;
using usm.SolDev.Common;
using usm.SolDev.DataBase.DataObjects;
using MySql.Data.MySqlClient;
using usm.SolDev.Common.Constants;

namespace usm.SolDev.DataBase
{
    /// <summary>
    ///     Data provider implementation
    /// </summary>
    public class MySQLDataProvider : IDataProvider
    {
        #region Internal data 
        private UserInfoData userInfoData = new UserInfoData();

        /// <summary>
        ///     Hold the information about database location. It is  required 
        /// to obtain a valid connection before to perform a query.
        /// </summary>
        private SQLConnectionString connectionString = new SQLConnectionString(true);
        #endregion

        #region Connection and reader open/close wrappers.
        /// <summary>
        ///     Open connection to database using passed dbString
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        private MySqlConnection OpenConnection(SQLConnectionString connectionString)
        {
            if (connectionString == null)
            {
                throw new ArgumentNullException("connection string");
            }

            MySqlConnection mysSqlConnection = new MySqlConnection(connectionString.BuildConnectionString());
            mysSqlConnection.Open();
            return mysSqlConnection;
        }

        /// <summary>
        ///     Test connection validity and close connection.
        /// </summary>
        /// <param name="connection">connection to close</param>
        private void CloseConnection (MySqlConnection connection)
        {
            if (connection != null)
            {
                connection.Close();
            }
        }

        /// <summary>
        ///     Test if reader has been opened and close it
        /// </summary>
        /// <param name="sqlReader">The SQL reader to close</param>
        private void CloseReader (MySqlDataReader sqlReader)
        {
            if (sqlReader != null)
            {
                sqlReader.Close();
            }
        }
        #endregion

        #region Safe SQL readers
        /// <summary>
        ///     Read SQL string safety. Test for null database value
        /// </summary>
        /// <param name="reader">the implied reader</param>
        /// <param name="index">the index of the item to read</param>
        /// <returns>the read value</returns>
        private string GetSQLString(MySqlDataReader reader, int index)
        {
            if (reader == null || reader.IsClosed || index < 0 || reader.IsDBNull(index))
            {
                return String.Empty;
            }

            return reader.GetString(index);
        }

        /// <summary>
        ///     Read SQL int32 safety. Test for null database value
        /// </summary>
        /// <param name="reader">the implied reader</param>
        /// <param name="index">the index of the item to read</param>
        /// <returns>the read value</returns>
        private int GetSQLInt32(MySqlDataReader reader, int index)
        {
            if (reader == null || reader.IsClosed || index < 0 || reader.IsDBNull(index))
            {
                return 0;
            }

            return reader.GetInt32(index);
        }

        /// <summary>
        ///     Read SQL GUID safety. Test for null database value
        /// </summary>
        /// <param name="reader">the implied reader</param>
        /// <param name="index">the index of the item to read</param>
        /// <returns>the read value</returns>
        private Guid GetSQLGuid(MySqlDataReader reader, int index)
        {
            if (reader == null || reader.IsClosed || index < 0 || reader.IsDBNull(index))
            {
                return new Guid();
            }

            return reader.GetGuid(index);
        }

        /// <summary>
        ///     Read SQL DateTime safety. Test for null database value
        /// </summary>
        /// <param name="reader">the implied reader</param>
        /// <param name="index">the index of the item to read</param>
        /// <returns>the read value</returns>
        private DateTime GetSQLDateTime(MySqlDataReader reader, int index)
        {
            if (reader == null || reader.IsClosed || index < 0 || reader.IsDBNull(index))
            {
                return DateTime.Now;
            }

            return reader.GetDateTime(index);
        }

        #endregion

        #region Test connection
        /// <summary>
        ///     Test if connection string parameters are valid. Just close the
        /// opened connection. If some data are invalid, an exception is thrown.
        /// </summary>
        /// <param name="connectionString"></param>
        /// <exception ref="MySqlException">Is thrown if connection data is invalid</exception>
        public void TestConnection(SQLConnectionString connectionString)
        {
            CloseConnection(OpenConnection(connectionString));
        }
        #endregion
       
        #region User related code
        /// <summary>
        ///     Get user data by user login and password. If they do not match 
        /// any existing, a null object is returned.
        /// </summary>
        /// <param name="userName">the user login</param>
        /// <param name="userPassword">the user password</param>
        /// <returns>user info descriptor</returns>
        public UserInfoData GetUserInfoData(string userName, string userPassword)
        {
            #region Data declaration
            MySqlConnection sqlConnection = null;
            MySqlDataReader sqlReader = null;
            UserInfoData    userInfoData = null;
            #endregion

            #region SQL Queries
            string SQL_GET_ID_BY_LOGIN_AND_PASSWD = @"SELECT `id`, `login`, `password`, `name`, `group_id` FROM users WHERE login = '{0}' AND password = '{1}'";
            string SQL_GET_USER_PERMISSIONS = @"
                            SELECT
                                `user_permission`.`permission_id`,
                                `permissions`.`name`,
                                `permissions`.`description`
                                
                            FROM
                                `user_permission`
                                
                            LEFT JOIN
                                `permissions`
                                 ON
                                 `user_permission`.`permission_id` = `permissions`.`id`
                                
                            WHERE
                                 `user_permission`.`user_id` = {0};";
            #endregion

            StringBuilder sqlScript = new StringBuilder();
            sqlScript.AppendFormat(SQL_GET_ID_BY_LOGIN_AND_PASSWD, userName, userPassword);

            try
            {
                sqlConnection = OpenConnection(connectionString);

                #region Load user info data
                MySqlCommand sqlCommand = new MySqlCommand(sqlScript.ToString(), sqlConnection);

                sqlReader = sqlCommand.ExecuteReader();
                if (sqlReader != null && sqlReader.HasRows == true && sqlReader.Read ())
                {
                    userInfoData = new UserInfoData (
                            GetSQLInt32(sqlReader, 0), // Id
                            GetSQLString(sqlReader, 1), // login
                            GetSQLString(sqlReader, 2), // password
                            GetSQLString(sqlReader, 3), // name
                            GetSQLInt32(sqlReader, 4)  // group id
                        );
                }

                if (userInfoData == null)
                {
                    throw new SolDevException("Cannot get user related information");
                }

                CloseReader(sqlReader);
                #endregion

                #region Read user permissions
                sqlScript = new StringBuilder();
                sqlScript.AppendFormat(SQL_GET_USER_PERMISSIONS, userInfoData.Id);
                sqlCommand = new MySqlCommand(sqlScript.ToString(), sqlConnection);

                sqlReader = sqlCommand.ExecuteReader();
                if (sqlReader != null && sqlReader.HasRows == true)
                {
                    while (sqlReader.Read())
                    {
                        userInfoData.UserPermitions.Add(new Permissions(
                                    (Permissions.UserPermission)sqlReader.GetInt32(0), // Id
                                    GetSQLString(sqlReader, 1), // permission
                                    GetSQLString(sqlReader, 2) // description
                            ));
                    }
                }

                if (userInfoData.UserPermitions == null)
                {
                    throw new SolDevException("Cannot get user permission");
                }
                #endregion

                return userInfoData;
            }
            catch (Exception ex)
            {
                // a-sync call of this method. We will not throw an exception. Just show error message
                new SolDevException(String.Format("failed to login to server: {0}", ex.ToString()));
            } finally
            {
                CloseReader(sqlReader);
                CloseConnection(sqlConnection);
            }

            return null;
        }
        #endregion

        #region Repository access
        /// <summary>
        ///     Get the projects list and content. This member function will 
        /// return queries for current user, or for user group for which it 
        /// belongs to.
        /// </summary>
        /// <param name="userInfoData">user related information</param>
        /// <returns></returns>
        public List<ProjectDescriptor> LoadRepositoryContent(UserInfoData userInfoData)
        {
            #region My SQL objects
            MySqlConnection sqlConnection = null;
            MySqlDataReader sqlReader = null;
            StringBuilder sqlScript = new StringBuilder();
            #endregion

            #region Sql queries

            #region SQL_LIST_USER_PROJECTS
            const string SQL_LIST_USER_PROJECTS = @"
                                    SELECT
                                         `repository`.`id`,
                                         UNCOMPRESS(UNHEX(`repository`.`program`)) as program ,
                                         UNCOMPRESS(UNHEX(`repository`.`results`)) as results,
                                         UNCOMPRESS(UNHEX(`repository`.`entities`)) as entities,
                                         `repository`.`user_id`,
                                         `repository`.`mark`,
                                         `repository`.`description`,
                                         `repository`.`last_edit_date`,

                                         `users`.`Name`,
                                         `groups`.`name`

                                    FROM
                                         `repository`
                                    LEFT JOIN
                                         `users`
                                         ON
                                         `users`.`id` = `repository`.`user_id`
                                    LEFT JOIN
                                         `groups`
                                         ON
                                         `groups`.`id` = `users`.`group_id`


                                    WHERE
                                         `repository`.`user_id` = {0};";
            #endregion

            #region SQL_LIST_GROUP_PROJECTS
            const string SQL_LIST_GROUP_PROJECTS = @"
                                    SELECT
                                         `repository`.`id`,
                                         UNCOMPRESS(UNHEX(`repository`.`program`)) as program ,
                                         UNCOMPRESS(UNHEX(`repository`.`results`)) as results,
                                         UNCOMPRESS(UNHEX(`repository`.`entities`)) as entities,
                                         `repository`.`user_id`,
                                         `repository`.`mark`,
                                         `repository`.`description`,
                                         `repository`.`last_edit_date`,

                                         `users`.`Name`,
                                         `groups`.`name`
                                         
                                    FROM
                                         `repository`
                                    LEFT JOIN
                                         `users`
                                          ON
                                          `users`.`group_id` = 1
                                    LEFT JOIN
                                         `groups`
                                         ON
                                         `groups`.`id` = `users`.`group_id`

                                    WHERE
                                         `repository`.`user_id` = `users`.`id`;";
            #endregion
            #endregion

            #region Build SQL query (depending on user settings)
            if (userInfoData.HasPermission(Permissions.UserPermission.ViewGroupsProjects) == true)
            {
                sqlScript.AppendFormat(SQL_LIST_GROUP_PROJECTS, userInfoData.Group);
            }
            else
            {
                sqlScript.AppendFormat(SQL_LIST_USER_PROJECTS, userInfoData.Id);
            }
            #endregion

            #region Read content
            try
            {
                sqlConnection = OpenConnection(connectionString);
                MySqlCommand sqlCommand = new MySqlCommand(sqlScript.ToString(), sqlConnection);

                sqlReader = sqlCommand.ExecuteReader();

                if (sqlReader != null && sqlReader.HasRows == true)
                {

                    List<ProjectDescriptor> projects = new List<ProjectDescriptor>();

                    while (sqlReader.Read())
                    {
                        ProjectDescriptor project = new ProjectDescriptor
                            (
                                GetSQLGuid(sqlReader, 0),
                                GetSQLString(sqlReader, 1),
                                GetSQLString(sqlReader, 2),
                                GetSQLString(sqlReader, 3),
                                GetSQLInt32(sqlReader, 4),
                                GetSQLInt32(sqlReader, 5),
                                GetSQLString(sqlReader, 6),
                                GetSQLDateTime(sqlReader, 7),

                                GetSQLString(sqlReader, 8),
                                GetSQLString(sqlReader, 9)
                            );

                        project.Origin = Application.Origins.fromDatabase;
                        projects.Add(project);
                    }

                    return projects;
                }
            }
            catch (Exception ex)
            {
                new SolDevException("Cannot complete SQL request", ex);

            }
            finally
            {
                CloseReader(sqlReader);
                CloseConnection(sqlConnection);
            }

            #endregion

            return null;
        }

        /// <summary>
        ///     Save project to Database 
        /// </summary>
        /// <param name="userInfoData">full information about current user</param>
        /// <param name="project">project information</param>
        public bool SaveProjectToRepository(ProjectDescriptor project)
        {
            #region Data declaration
            MySqlConnection sqlConnection = null;
            MySqlDataReader sqlReader = null;
            MySqlTransaction sqlTransaction = null;

            #endregion

            #region SQL queries

            #region SQL_GET_EXISTENT_PROJECT
            const string SQL_GET_EXISTENT_PROJECT = @"SELECT `repository`.`id` FROM `repository` WHERE `repository`.`id` = '{0}';";
            #endregion

            #region SQL_UPDATE_EXISTENT_PROJECT
            const string SQL_UPDATE_EXISTENT_PROJECT = @"
                                    UPDATE
                                       `repository`
                                    SET
                                       `program` = HEX(COMPRESS(?program)),
                                       `results` = HEX(COMPRESS(?result)),
                                        `entities` = HEX(COMPRESS(?entities)),
                                       `user_id` = ?userId,
                                       `mark` = ?mark,
                                       `description` = ?description,
                                       `last_edit_date` = ?lastEditDate
                                    WHERE
                                        `id` = ?id;";
            #endregion

            #region SQL_INSERT_NEW_PROJECT
            const string SQL_INSERT_NEW_PROJECT = @"
                                    INSERT INTO `repository`
                                       (`id`, `program`, `results`, `entities`, `user_id`, `mark`, `description`, `last_edit_date` )
                                       
                                    VALUES
                                        (?id, HEX(COMPRESS(?program)), HEX(COMPRESS(?result)), HEX(COMPRESS(?entities)), ?userId, ?mark, ?description, ?lastEditDate)";
            #endregion

            #endregion

            try
            {
                sqlConnection = OpenConnection(connectionString);
                sqlTransaction = sqlConnection.BeginTransaction();

                MySqlCommand sqlCommand = new MySqlCommand(String.Format(SQL_GET_EXISTENT_PROJECT, project.Id), sqlConnection, sqlTransaction);
                sqlReader = sqlCommand.ExecuteReader();
                StringBuilder sqlQuery = new StringBuilder();

                if (sqlReader != null && sqlReader.HasRows == true)
                {
                    // the project already exists
                    CloseReader(sqlReader);
                    sqlCommand = new MySqlCommand(SQL_UPDATE_EXISTENT_PROJECT, sqlConnection, sqlTransaction);
                }
                else
                {
                    // insert new project
                    CloseReader(sqlReader);
                    sqlCommand = new MySqlCommand(SQL_INSERT_NEW_PROJECT, sqlConnection, sqlTransaction);
                }


                sqlCommand.Parameters.Add("?id", project.Id);
                sqlCommand.Parameters.Add("?program", project.Program);
                sqlCommand.Parameters.Add("?result", project.Results);
                sqlCommand.Parameters.Add("?entities", project.Entities);
                sqlCommand.Parameters.Add("?userId", project.UserId);
                sqlCommand.Parameters.Add("?mark", project.Mark);
                sqlCommand.Parameters.Add("?description", project.Description);
                sqlCommand.Parameters.Add("?lastEditDate", project.LastEditTime);

                sqlCommand.ExecuteNonQuery();
                sqlTransaction.Commit();
                return true;

            } catch (Exception ex)
            {
                sqlTransaction.Rollback();
                new SolDevException ("Cannot save project", ex);
            }
            finally
            {
                CloseReader(sqlReader);
                CloseConnection(sqlConnection);
            }

            return false;
        }
        
        /// <summary>
        ///     Delete a project from Database 
        /// </summary>
        /// <param name="project">project related information</param>
        /// <returns>the operation result</returns>
        public string DeleteProjectFromRepository(ProjectDescriptor project, UserInfoData userInfo)
        {
            #region Data declaration
            MySqlConnection sqlConnection = null;
            #endregion

            #region SQL queries
            const string SQL_DELETE_EXISTENT_PROJECT = @"DELETE FROM  `repository` WHERE `repository`.`id` = '{0}';";
            #endregion

            #region Check the parameters
            if (project == null)
            {
                throw new ArgumentNullException("project");
            }
            #endregion

            #region execute delete query
            try
            {
                sqlConnection = OpenConnection(connectionString);
                MySqlCommand sqlCommand = new MySqlCommand(String.Format(SQL_DELETE_EXISTENT_PROJECT, project.Id), sqlConnection);
                sqlCommand.ExecuteNonQuery();
                return project.Id.ToString();
            }
            catch (Exception ex)
            {
                new SolDevException("Cannot delete project", ex);
            }
            finally
            {
                CloseConnection(sqlConnection);
            }
            #endregion

            return null;
        }

        #endregion
    
        #region User and Group management

        /// <summary>
        ///     Read all data required for user management from database. 
        /// </summary>
        /// <param name="currentUser">the current user whose rights are checked.</param>
        /// <returns>the structure which contains all information about user managment</returns>
        public UserManagementData GetUserManagementData (UserInfoData currentUser)
        {
            #region Sql declarations 
            const string SQL_SELECT_AVAILABLE_GROUPS = " SELECT id, name FROM groups; ";
            const string SQL_SELECT_AVAILABLE_USERS = " SELECT id, login, name, group_id, password FROM users; ";
            const string SQL_SELECT_AVAILABLE_PERMISSIONS = " SELECT id, name, description FROM permissions; ";
            const string SQL_SELECT_ASSIGNED_PERMISSIONS = " SELECT id, user_id, permission_id FROM user_permission; ";
            #endregion

            #region Check perimissions
            if (currentUser == null || currentUser.HasPermission(Permissions.UserPermission.ManageUsers) == false)
            {
                throw new SolDevException("You do not have permissions");
            }
            #endregion

            #region Variable declarations
            MySqlConnection sqlConnection = null;
            MySqlDataReader sqlReader = null;
            UserManagementData userManagementData = new UserManagementData();

            #endregion

            try
            {
                #region Read available groups
                sqlConnection = OpenConnection(connectionString);
                MySqlCommand sqlCommand = new MySqlCommand(SQL_SELECT_AVAILABLE_GROUPS, sqlConnection);
                if ((sqlReader = sqlCommand.ExecuteReader()) != null && sqlReader.HasRows == true)
                {
                    while (sqlReader.Read())
                    {
                        userManagementData.Groups.Add(new GroupDataObject(GetSQLInt32(sqlReader, 0), GetSQLString(sqlReader, 1)));
                    }
                }
                CloseReader(sqlReader);
                #endregion

                #region Read available users
                sqlCommand = new MySqlCommand(SQL_SELECT_AVAILABLE_USERS, sqlConnection);
                if ((sqlReader = sqlCommand.ExecuteReader()) != null && sqlReader.HasRows == true)
                {
                    while (sqlReader.Read())
                    {
                        userManagementData.Users.Add(new UserInfoData(GetSQLInt32(sqlReader, 0), sqlReader.GetString(1), GetSQLString(sqlReader, 4), GetSQLString(sqlReader, 2), GetSQLInt32(sqlReader, 3)));
                    }
                }
                CloseReader(sqlReader);
                #endregion

                #region Read available permissions
                sqlCommand = new MySqlCommand(SQL_SELECT_AVAILABLE_PERMISSIONS, sqlConnection);
                if ((sqlReader = sqlCommand.ExecuteReader()) != null && sqlReader.HasRows == true)
                {
                    while (sqlReader.Read())
                    {
                        userManagementData.Permissions.Add(new Permissions((Permissions.UserPermission)GetSQLInt32(sqlReader, 0), GetSQLString(sqlReader, 1), GetSQLString(sqlReader, 2)));
                    }
                }
                CloseReader(sqlReader);
                #endregion

                #region Read assigned permitions
                sqlCommand = new MySqlCommand(SQL_SELECT_ASSIGNED_PERMISSIONS, sqlConnection);
                if ((sqlReader = sqlCommand.ExecuteReader()) != null && sqlReader.HasRows == true)
                {
                    while (sqlReader.Read())
                    {
                        int userId = GetSQLInt32(sqlReader, 1);
                        foreach(UserInfoData userInfo in userManagementData.Users)
                        {
                            if (userInfo.Id == userId)
                            {
                                userInfo.UserPermitions.Add(Permissions.GePermitionDescriptor(GetSQLInt32(sqlReader, 2), userManagementData.Permissions));
                            }
                        }
                    }
                }
                #endregion

                return userManagementData;

            } catch (Exception ex)
            {
                new SolDevException ("Cannot load user management data", ex);
            }
            finally
            {
                CloseReader(sqlReader);
                CloseConnection(sqlConnection);
            }

            return null;
        }

        /// <summary>
        ///     Save user and group related modifications. This method actually calls 
        /// ManageGroup and ManageUsers methods with respective parameter
        /// </summary>
        /// <param name="currentUser">the current user who is requesting the operation</param>
        /// <param name="usersDataList">the list of users to be saved</param>
        /// <param name="groupDataList">the list of groups to be saved</param>
        /// <returns>an array formed from usersDataList and groupDataList with updated id's</returns>
        public object [] SaveUserManagementData(UserInfoData currentUser, List<UserInfoData> usersDataList, List<GroupDataObject> groupDataList)
        {
            MySqlConnection sqlConnection = null;
            MySqlTransaction sqlTransaction = null;
            try
            {
                sqlConnection = OpenConnection(connectionString);
                sqlTransaction = sqlConnection.BeginTransaction ();
                
                usersDataList = ManageUsers(currentUser, usersDataList, sqlTransaction);
                groupDataList = ManageGroup(currentUser, groupDataList, sqlTransaction);

                sqlTransaction.Commit();
                return new object[] { usersDataList, groupDataList };
                

            } catch (Exception ex)
            {
                if (sqlTransaction != null)
                {
                    sqlTransaction.Rollback();
                }
                new SolDevException(ex.Message);
            }
            finally
            {
                CloseConnection(sqlConnection);
            }

            return null;
        }

        /// <summary>
        ///     Manage group. Depending on object state, New, Modified or Deleted
        /// the group described by groupDataList parameter
        /// </summary>
        /// <param name="groupData"></param>
        /// <returns>a group data object. If the object is newly creted, the 
        /// id of the group is returned </returns>
        private List<GroupDataObject> ManageGroup(UserInfoData currentUser, List<GroupDataObject> groupDataList, MySqlTransaction transact)
        {
            #region Check parameteres
            if (groupDataList == null)
            {
                throw new Exception("Null parameter 'groupDataList' passed");
            }
            #endregion

            #region Check users count
            if (groupDataList.Count == 0)
            {
                return groupDataList;
            }
            #endregion

            #region Check permisions
            if (currentUser.HasPermission(Permissions.UserPermission.ManageUsers) == false)
            {
                throw new Exception("Access denied. You have no rights to manage users and groups");
            }
            #endregion

            #region Declarations
            string sqlQueryText = "";
            // make the scripts uniformed. All the queries must accept 2 parameters and return id of the group
            const string SQL_CREATE_GROUP = @" INSERT INTO groups (id, name) values (NULL, {0}); SELECT LAST_INSERT_ID(); ";
            const string SQL_MODIFY_GROUP = @" UPDATE groups SET name = '{0}' WHERE id = {1}; SELECT {1}; ";
            const string SQL_DELETE_GROUP = @" DELETE FROM groups WHERE id = {0}; DELETE FROM users where group_id = {0}; SELECT {0}; ";
            #endregion

            #region Query database
            foreach (GroupDataObject groupData in groupDataList)
            {
                #region Check if data hase been modified
                if (groupData.State == usm.SolDev.Common.Config.ObjectState.Original)
                {
                    continue;
                }
                #endregion

                #region Build query string
                switch (groupData.State)
                {
                    case usm.SolDev.Common.Config.ObjectState.New:
                        sqlQueryText = String.Format(SQL_CREATE_GROUP, groupData.Name);
                        break;

                    case usm.SolDev.Common.Config.ObjectState.Deleted:
                        sqlQueryText = String.Format(SQL_DELETE_GROUP, groupData.Id);
                        break;

                    case usm.SolDev.Common.Config.ObjectState.Modified:
                        sqlQueryText = String.Format(SQL_MODIFY_GROUP, groupData.Name, groupData.Id);
                        break;

                    default:
                        continue;
                }
                #endregion
                
                MySqlCommand sqlCommand = new MySqlCommand(sqlQueryText, transact.Connection, transact);
                groupData.Id = int.Parse(sqlCommand.ExecuteScalar().ToString());
                groupData.State = usm.SolDev.Common.Config.ObjectState.Original;
            }
            #endregion

            #region Reload and reset status of each data object
            List<GroupDataObject> newGroupDataList = new List<GroupDataObject>();
            foreach (GroupDataObject groupData in groupDataList)
            {
                if (groupData.State != usm.SolDev.Common.Config.ObjectState.Deleted)
                {
                    groupData.State = usm.SolDev.Common.Config.ObjectState.Original;
                    newGroupDataList.Add(groupData);
                }
            }
            #endregion

            return newGroupDataList;
       }
        
        /// <summary>
        ///     Manage users. Depending on object state, New, Modified or Deleted
        /// the user described by items of the usersDataList parameter
        /// </summary>
        /// <param name="usersDataList">a list of users which were modified or 
        /// created</param>
        /// <returns>the same user list</returns>
        private List<UserInfoData> ManageUsers(UserInfoData currentUser, List<UserInfoData> usersDataList, MySqlTransaction transact)
        {
            #region Check parameteres
            if (usersDataList == null)
            {
                throw new SolDevException("Null parameter 'usersDataList' passed");
            }
            #endregion

            #region Check users count
            if (usersDataList.Count == 0)
            {
                return usersDataList;
            }
            #endregion

            #region Check permisions
            if (currentUser.HasPermission(Permissions.UserPermission.ManageUsers) == false)
            {
                throw new Exception("Access denied. You have no rights to manage users and groups");
            }
            #endregion

            #region Declarations
            MySqlCommand sqlCommand = null;
            

            const string SQL_CREATE_USER = @" INSERT INTO users (id, login, password, Name, group_id) VALUES ( NULL, '{0}', '{1}', '{2}', {3}); SELECT LAST_INSERT_ID(); ";
            const string SQL_UPDATE_USER = @" UPDATE users SET login = '{0}' , password = '{1}' , Name = '{2}', group_id = {4} WHERE id = {3}; ";
            const string SQL_DELETE_USER = @" DELETE FROM users WHERE id = {0}; ";

            const string SQL_CREATE_PERMISSION = @" INSERT INTO user_permission (id, user_id, permission_id) VALUES  (NULL, {0}, {1}); ";
            const string SQL_DELETE_PERMISSION = @" DELETE FROM user_permission WHERE user_id = {0}; ";
            #endregion

            #region Query database
            foreach (UserInfoData userData in usersDataList)
            {
                StringBuilder sqlQueryText = new StringBuilder();
                #region Check if data has been modified
                if (userData.State == usm.SolDev.Common.Config.ObjectState.Original)
                {
                    continue;
                }
                #endregion

                #region Build query string
                switch (userData.State)
                {
                    #region Create new user
                    case usm.SolDev.Common.Config.ObjectState.New:
                        sqlQueryText.AppendFormat(SQL_CREATE_USER, userData.Login, userData.Password, userData.Name, userData.Group);

                        sqlCommand = new MySqlCommand(sqlQueryText.ToString(), transact.Connection, transact);
                        userData.Id = int.Parse(sqlCommand.ExecuteScalar().ToString());

                        sqlQueryText = new StringBuilder();
                        sqlQueryText.AppendFormat(SQL_DELETE_PERMISSION, userData.Id);
                        foreach (Permissions userPermition in userData.UserPermitions)
                        {
                            sqlQueryText.AppendFormat(SQL_CREATE_PERMISSION, userData.Id, (int)userPermition.Id);
                        }

                        sqlCommand = new MySqlCommand(sqlQueryText.ToString(), transact.Connection, transact);
                        sqlCommand.ExecuteNonQuery();
                        break;
                    #endregion

                    #region Remove user and permission
                    case usm.SolDev.Common.Config.ObjectState.Deleted:
                        sqlQueryText.AppendFormat(SQL_DELETE_USER, userData.Id);
                        sqlQueryText.AppendFormat(SQL_DELETE_PERMISSION, userData.Id);

                        sqlCommand = new MySqlCommand(sqlQueryText.ToString(), transact.Connection, transact);
                        sqlCommand.ExecuteNonQuery();
                        break;
                    #endregion

                    #region Update user related data
                    case usm.SolDev.Common.Config.ObjectState.Modified:
                        sqlQueryText.AppendFormat(SQL_UPDATE_USER, userData.Login, userData.Password, userData.Name, userData.Id, userData.Group);
                        sqlQueryText.AppendFormat(SQL_DELETE_PERMISSION, userData.Id);

                        foreach (Permissions userPermition in userData.UserPermitions)
                        {
                            sqlQueryText.AppendFormat(SQL_CREATE_PERMISSION, userData.Id, (int)userPermition.Id);
                        }

                        sqlCommand = new MySqlCommand(sqlQueryText.ToString(), transact.Connection, transact);
                        sqlCommand.ExecuteNonQuery();
                        break;
                    #endregion

                    default:
                        continue;
                }
                #endregion

            }
            #endregion

            #region Reload and reset status of each data object
            List<UserInfoData> newUserDataList = new List<UserInfoData>();
            foreach (UserInfoData userData in usersDataList)
            {
                if (userData.State != usm.SolDev.Common.Config.ObjectState.Deleted)
                {
                    userData.State = usm.SolDev.Common.Config.ObjectState.Original;
                    newUserDataList.Add(userData);
                }
            }
            #endregion

            return newUserDataList;
        }
        #endregion
    }
}
